package muni.ilo.dao;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import muni.ilo.dto.CartaDesaprobacionDTO;
import muni.ilo.dto.InforLicObraDTO;
import muni.ilo.dto.InformeCompletoDTO;
import muni.ilo.dto.SolicitudContDTO;
import muni.ilo.util.MySqlDataSource;

public class MySQLLicenciaDAO implements LicenciaDAO_I {

	public List<SolicitudContDTO> listaSolAprobDesap() throws Exception {
		List<SolicitudContDTO> lstSolicitudes = new ArrayList<SolicitudContDTO>();
		Connection cn = MySqlDataSource.getConnection();
		String sql = "select codSoli from solicitud " +
					"where estAprobacionJefeLicencia in (?,?)";
		
		PreparedStatement pst = cn.prepareStatement(sql);
		pst.setString(1, "APROBADO");
		pst.setString(2, "DESAPROBADO");
		
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			
			SolicitudContDTO objSolicitud = new SolicitudContDTO();
			objSolicitud.setStrCodSol(rs.getString(1));
			
			lstSolicitudes.add(objSolicitud);
		}
		cn.close();
		
		return lstSolicitudes;
	}

	public CartaDesaprobacionDTO obtenerCarta(String strCodSoli)
			throws Exception {
		CartaDesaprobacionDTO objCarta = null;
		Connection cn = MySqlDataSource.getConnection();
		String sql = "select ca.codCarta,ca.observaciones,ca.codSoli,i.codigoInf,c.nombreContri, " +
					"c.apepaterContri,c.apematerContri,u.nombreUsuario,u.apepaterUsuario,u.apematerUsuario " +
					"from cartadesaprobacion ca inner join solicitud s on ca.codSoli = s.codSoli inner join " +
					"`comprobante de pago` p on s.codPago = p.codPago inner join contribuyente c " +
					"on p.codContri = c.codContri inner join informe i on s.codSoli = i.codSoli inner join " +
					"usuario u on s.codTecnico = u.codUsuario where ca.codSoli = ? order by 1 desc limit 1";
		
		PreparedStatement pst = cn.prepareStatement(sql);
		pst.setString(1, strCodSoli);
		ResultSet rs = pst.executeQuery();
		
		while(rs.next()){
			
			objCarta = new CartaDesaprobacionDTO();
			objCarta.setStrCodCarta(rs.getString(1));
			objCarta.setStrObservaciones(rs.getString(2));
			objCarta.setStrCodSoli(rs.getString(3));
			objCarta.setStrCodigoInf(rs.getString(4));
			objCarta.setStrNombreContri(rs.getString(5));
			objCarta.setStrApePaterContri(rs.getString(6));
			objCarta.setStrApeMaterContri(rs.getString(7));
			objCarta.setStrNombreUsu(rs.getString(8));
			objCarta.setStrApePaterUsu(rs.getString(9));
			objCarta.setStrApeMaterUsu(rs.getString(10));
			
		}
		cn.close();
		
		return objCarta;
	}

	public InforLicObraDTO obtenerLicencia(String strCodSoli) throws Exception {
		InforLicObraDTO objInfLicOb = null;
		Connection cn = MySqlDataSource.getConnection();
		String sql = "select i.codSoli,l.codColArqui,l.nombreArquitec,l.vigenciaObraMeses,o.areaTotalTechada, " +
					"o.areaConstruida1PM2,o.nroPisosTotales,o.areaConstruida2PM2,o.areaConstruida3PM2,o.ubicacion, " +
					"o.areaConstruida4PM2,o.areaTerrenoTotal,u.descripcion,s.codPago,p.montoTotal,c.nombreContri, " +
					"c.apepaterContri,c.apematerContri from informe i inner join licencia l on i.codSoli = l.codSoli " +
					"inner join obra o on i.codSoli = o.codSoli inner join solicitud s on i.codSoli = s.CodSoli inner join " +
					"`comprobante de pago` p on s.codPago = p.codPago inner join contribuyente c on p.codContri = c.codContri " +
					" inner join `uso de edificacion` u on o.codUsoEdif = u.codUsoEdif where s.codSoli = ?";
		
		PreparedStatement pst = cn.prepareStatement(sql);
		pst.setString(1, strCodSoli);
		
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			
			objInfLicOb = new InforLicObraDTO();
			objInfLicOb.setStrCodSoli(rs.getString(1));
			objInfLicOb.setStrCodColArqui(rs.getString(2));
			objInfLicOb.setStrNombreArquitec(rs.getString(3));
			objInfLicOb.setIntVigenciaObraMeses(rs.getInt(4));
			objInfLicOb.setDbAreaTotalTechada(rs.getDouble(5));
			objInfLicOb.setDbAreaConstruida1pm2(rs.getDouble(6));
			objInfLicOb.setIntNroPisosTotales(rs.getInt(7));
			objInfLicOb.setDbAreaConstruida2pm2(rs.getDouble(8));
			objInfLicOb.setDbAreaConstruida3pm2(rs.getDouble(9));
			objInfLicOb.setStrUbicacion(rs.getString(10));
			objInfLicOb.setDbAreaConstruida4pm2(rs.getDouble(11));
			objInfLicOb.setDbAreaTerrenoTotal(rs.getDouble(12));
			objInfLicOb.setStrUsoEdificacion(rs.getString(13));
			objInfLicOb.setStrCodPago(rs.getString(14));
			objInfLicOb.setDbMontoTotal(rs.getDouble(15));
			objInfLicOb.setStrNombreContri(rs.getString(16));
			objInfLicOb.setStrApePaterContri(rs.getString(17));
			objInfLicOb.setStrApeMaterContri(rs.getString(18));
			
		}
		cn.close();
		
		return objInfLicOb;
	}

	public InformeCompletoDTO obtenerSolicitud(String strCodSoli)
			throws Exception {
		InformeCompletoDTO objInfComp = null;
		Connection cn = MySqlDataSource.getConnection();
		String sql = "select s.codSoli,s.fecRegSol,i.codigoInf,i.fechaCreacion, " +
					"c.nombreContri,c.apepaterContri,c.apematerContri,u.nombreUsuario, " +
					"u.apepaterUsuario,u.apematerUsuario,u.codUsuario,s.EstadoEntregaLic, " +
					"s.estAprobacionJefeLicencia from solicitud s inner join " +
					"`comprobante de pago` p on s.codPago = p.codPago inner join " +
					"contribuyente c on p.codContri = c.codContri inner join informe i " +
					"on s.codSoli = i.codSoli inner join usuario u on s.codTecnico = u.codUsuario " +
					"where s.codSoli = ?";
		
		PreparedStatement pst = cn.prepareStatement(sql);
		pst.setString(1, strCodSoli);
		
		ResultSet rs = pst.executeQuery();
		while(rs.next()){
			
			objInfComp = new InformeCompletoDTO();
			objInfComp.setStrCodSoli(rs.getString(1));
			objInfComp.setDtFechaPago(rs.getDate(2));
			objInfComp.setStrCodigoInf(rs.getString(3));
			objInfComp.setDtFechaCreacion(rs.getDate(4));
			objInfComp.setStrNombreContri(rs.getString(5));
			objInfComp.setStrApePaterContri(rs.getString(6));
			objInfComp.setStrApeMaterContri(rs.getString(7));
			objInfComp.setStrNombreUsu(rs.getString(8));
			objInfComp.setStrApePaterUsu(rs.getString(9));
			objInfComp.setStrApeMaterUsu(rs.getString(10));
			objInfComp.setStrCodUsu(rs.getString(11));
			objInfComp.setStrEstEntregaLic(rs.getString(12));
			objInfComp.setStrEstAprobJefeLic(rs.getString(13));
			
		}
		cn.close();
		
		return objInfComp;
	}

	public String registrarEntrega(String strCodSoli) throws Exception {
		String strResp = null;
		Connection cn = MySqlDataSource.getConnection();
		PreparedStatement pst = null;
		java.util.Date utilDate = new java.util.Date();
		java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		Calendar c1=Calendar.getInstance();
		StringBuffer hora=new StringBuffer();
		hora.append(c1.get(Calendar.HOUR_OF_DAY));
		hora.append(" : ");
		if(c1.get(Calendar.MINUTE)<=9){
		hora.append("0");
		}
		hora.append(c1.get(Calendar.MINUTE));
		int intCountS = 0;
		int intCountI = 0;
		int intCountL = 0;
		try{
			cn.setAutoCommit(false);
			String sql1 = "update solicitud set EstadoEntregaLic = ?,estAsignacionTec = ? " +
						"where codSoli = ?";
			pst = cn.prepareStatement(sql1);
			pst.setString(1, "ENTREGADO");
			pst.setString(2, "FINALIZADO");
			pst.setString(3, strCodSoli);
			
			intCountS = pst.executeUpdate();
			//--
			String sql2 = "update informe set estaAvancInforme = ? where codSoli = ?";
			pst = cn.prepareStatement(sql2);
			pst.setString(1, "FINALIZADO");
			pst.setString(2, strCodSoli);
			
			intCountI = pst.executeUpdate();
			//--
			String sql3 = "update licencia set fechaEntrega = ?,horaEntrega = ? " +
						"where codSoli = ?";
			pst = cn.prepareStatement(sql3);
			pst.setDate(1, sqlDate);
			pst.setString(2, hora.toString());
			pst.setString(3, strCodSoli);
			
			intCountL = pst.executeUpdate();
			//--
			if(intCountS > 0 && intCountI > 0 && intCountL > 0){
				cn.commit();
				strResp = "Estado de Licencia actualizado como Entregado, proceda a imprimir";
			}else{
				cn.rollback();
			}
			
		}catch (Exception e) {
			cn.rollback();
			System.out.println("En MySQLLicenciaDAO:registrarEntrega");
			e.printStackTrace();
		}
		
		cn.close();
		
		return strResp;
	}

}
